The present invention relates, in general, to databases and methods for accessing such databases with query objects, and, in particular, to apparatus and methods for interactively constructing, testing and using such query objects.
Databases represent an important data processing area in many applications. They are often used to store large amounts of information and then efficiently retrieve selected portions of the information. Many present day systems use a client/server database approach in which application programs running in clients access a central database management system (DBMS) located in a database server. In order to efficiently access the information in the database, the clients form queries which request information with selected characteristics. The queries are transmitted to the DBMS server which retrieves the desired information which meets the characteristics specified in the query. The results (commonly called a xe2x80x9cresult setxe2x80x9d) are returned to the client.
Presently, such database environments are predominantly based on a xe2x80x9ctwo-tieredxe2x80x9d model consisting of a top tier containing one or more applications which generate queries that access the DBMS server in a bottom tier. The two-tiered model suffers from several drawbacks. First, the queries must be formulated in a specific query language, which is accepted by the DBMS server. While standard query languages exist, such as the Structured Query Language (SQL), specific DBMS query languages are often non-standard as a result of proprietary extensions made to the basic SQL query language. As a result, application programs written to generate queries in a particular query language are often not portable between different DBMS servers.
In addition, in order to generate the queries in the first place, each application developer, and, in some cases, the ultimate user, must understand the mechanics of the database, including the relationship of the files and tables therein and any relationships in the data required by the database organization. These relationships are commonly referred to as xe2x80x9cbusiness logicxe2x80x9d since the relationships are typically based on shared business processes, practices and policies. Therefore, many parties must learn the business logic in order to generate meaningful queries.
Further, commonly-performed routines are typically replicated in each application program even if the application programs operate within the same business environment because each application functions autonomously from other applications. This replication results in poor code re-use and maintenance problems if the replicated routines must be changed.
Consequently, there is a trend towards using a three-tiered model for database environments. Generally, the top tier in such a model consists of clients containing the application programs and the user interfaces, the middle tier consists of code that embodies the business logic and the bottom tier consists of the DBMS servers which contain the data. In this model, the applications are implemented as xe2x80x9cthinxe2x80x9d clients, all of which interface with the business logic by means of a common interface which does not involve knowledge of the business logic. The commonly-performed routiner are all consolidated into the middle tier as part of the business logic. Since the business logic is shareable between the clients, code replication is avoided. The Common Object Request Broker Architecture (CORBA) presents one object-oriented approach to forming a three-tiered database environment, such as described in R. Orfali et al., xe2x80x9cThe Essential Client/Server Survival Guide,xe2x80x9d pp. 375-458, John Wiley and Sons, Inc. (2d ed. 1996), the disclosure of which is incorporated herein by reference.
Several prior art methods of implementing the three-tiered model exist, however, most existing DBMS access mechanisms and tools, including fourth generation languages (4GLs) and application programming interfaces (APIs), have been designed for the two-tiered model and are ill-suited for use in the three-tiered model. Consequently, several prior art designs including xe2x80x9cdatabasexe2x80x9d objects and xe2x80x9cactive dataxe2x80x9d objects have their own strengths and drawbacks. One promising prior art approach to constructing a middle tier containing business logic uses xe2x80x9cquery objects.xe2x80x9d Each query object is a server object that:
(1) translates client method invocations into equivalent queries in a query language which is understood by a database;
(2) issues those queries to the database; and
(3) returns the results as strongly-typed data values.
The query object effectively encapsulates the DBMS specific query language so that the application programs do not have to know or use the DBMS query language. Query objects also encapsulate the database organization or xe2x80x9cschemaxe2x80x9d, so that query object clients are isolated from database changes (although the query object itself may have to be changed if the underlying database must be changed.) Query objects also present their clients with a DBMS independent API, for example, CORBA defines an Interface Definition Language (IDL) interface. They do not require that the data be mapped into objects as is the case with active data objects so that significant performance advantages can be obtained and concurrence issues avoided.
Each query object provides, as part of its interface, one or more parameterized methods. Calls on each method are translated by the query object into one or more standard queries such as SELECT, UPDATE, INSERT and DELETE or into the initiation of one or more stored procedures in the database. In order to use the query object, a client first establishes a connection to the query object via some mechanism, such as a CORBA naming service. One of the methods is invoked and the query object then executes the query. The method is then invoked to execute the query.
However, in order to operate properly, the query object must be constructed to generate the correct DBMS queries in response to client requests. Otherwise, the query object may operate as designed, but produce results which are not intended. Constructing a query object to generate the correct queries requires a knowledge of SQL, an understanding of the underlying database schema, the possible handling of intermediate results generated by the query and interpretation of the results. In addition, a query object developer must consider other issues such as connection to the database using CORBA or similar arrangement, concurrency problems and translation required between the interface used by the query object and the API used by the database. Consequently, tools have been developed to automate the generation of query objects. Some tools can generate a query object from a query described in conventional SQL language which is entered by a user.
However, even with such tools, it may be necessary to understand the schema of the underlying database in order to correctly construct the initial SQL query and map the SQL query into the query language used by the underlying database.
Further, a need for testing the generated query object with actual data exists. For example, the query may not have been correctly written by the user or may produce unexpected results. In order to generate a test facility for the query object, it may be necessary to understand the internal construction of the query object and its operation. If the query object was generated with an automated tool, information concerning the construction of the object and its operation may not be readily available and it may be necessary to examine the inner workings of the query object in order to properly construct a test platform. In addition, if the query object is used with a distributed object system, it may be necessary to first install, then locate the query object in order to test it.
While these latter operations are well-known, they can be time-consuming details which distract the user from other, more important work. It would be highly desirable to coordinate the creation, initialization and testing functions in an integrated environment, preferably an integrated visual environment. Such an environment would allow a user to easily view the specifics of the underlying database, quickly construct a query object, install the object and test it with actual input data and with the actual database on which it is to operate and then easily modify the object if it does not perform as expected and retest the object.
Finally, it would also be desirable to be able to easily connect such a development environment with databases sold by different vendors so that a consistent interface is presented to the user no matter which database is actually used to execute the query.
Therefore, there is a need to provide a visual integrated development environment for generating, installing, initializing and testing of query objects which can operate with different databases while presenting a consistent interface to a user.
The foregoing problems are solved in one illustrative embodiment of the invention in which a query object generator tool which generates interface definitions and code that implement a query object also generates a graphic user interface (GUI) for controlling the entire system and plug-in objects, including a database schema access query object and test objects for allowing the GUI to operate with vendor-specific databases.
The GUI has a fixed appearance which is xe2x80x9ccustomizedxe2x80x9d by the various plug-in objects. For example, the database schema access query object is designed specifically for a particular underlying database and retrieves xe2x80x9cmetadataxe2x80x9d concerning the database schema. The retrieved metadata is then displayed as part of the graphic user interface to assist the user in constructing a query object.
In accordance with one embodiment of the invention, the user writes a query which is to be implemented as part of a query object in a generic query language such as SQL. The database schema access object can be used to verify the syntax of the generic SQL query by converting the query to the vendor-specific language used by the database and submitting the query to the database engine.
In accordance with another embodiment of the invention test objects are also generated by the GUI in response to a user request. The test objects contain information that characterizes the query object for testing purposes. The information in the test objects is used with a test framework to install and initialize the query object. The test framework also uses the information in the test objects to customize part of the GUI in order to allow a user to view and interact with the installed query object. In particular, the customized GUI allows a developer to enter input parameters for a query directly from the interface and use the installed query object to perform a query with the input parameters. Results which are returned from the query are displayed on the interface.